﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_ETC_LEGO_MGT_Select_Paging')
	BEGIN
		DROP Procedure usp_UPDMS_ETC_LEGO_MGT_Select_Paging
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_ETC_LEGO_MGT_Select_Paging
**	Desc : 레고관리를 조회한다.
**	Test Exec Query : Exec usp_UPDMS_ETC_LEGO_MGT_Select_Paging 0, 20, '2011-01-01', '2014-10-01', '','','','','','AJ001', 'ASC', 'ko'
**	Called by : Etc_Dac_UPDMS_ETC_LEGO_MGT.cs
**	Program ID : Etc1014m
**	Auth : 송시명
**	Date : 2014-09-11
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_ETC_LEGO_MGT_Select_Paging]
@li_st_page int,
@li_ed_page int,
@ls_buy_dt_from nvarchar(10),
@ls_buy_dt_to nvarchar(10),
@ls_series nvarchar(max),
@ls_prod_no varchar(10),
@ls_prod_nm nvarchar(200),
@ls_contents nvarchar(max),
@ls_order_by nvarchar(5),
@ls_asc_desc nvarchar(5),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @sql nvarchar(max)

--시리즈
DECLARE @series nvarchar(max)
IF @ls_series = ''
BEGIN
    SET @series = '%'
END
ELSE
BEGIN
    SET @series = @ls_series
END

--정렬
DECLARE @order_by AS nvarchar(max)
IF @ls_order_by = 'AJ001' --구입일
BEGIN
    SET @order_by = 'Buy_Dt '
END
ELSE IF @ls_order_by = 'AJ002' --시리즈
BEGIN
    SET @order_by = 'Series '
END
ELSE IF @ls_order_by = 'AJ003' --제품번호
BEGIN
    SET @order_by = 'CONVERT(int, Prod_No) '
END
ELSE IF @ls_order_by = 'AJ004' --제품명
BEGIN
    SET @order_by = 'Prod_Nm '
END
ELSE IF @ls_order_by = 'AJ005' --브릭수
BEGIN
    SET @order_by = 'Brick_Cnt '
END
ELSE IF @ls_order_by = 'AJ006' --브릭무게
BEGIN
    SET @order_by = 'Brick_Weight '
END
ELSE IF @ls_order_by = 'AJ007' --피겨수
BEGIN
    SET @order_by = 'Figure_Cnt '
END
ELSE IF @ls_order_by = 'AJ008' --구입가격
BEGIN
    SET @order_by = 'Price '
END

SET @sql = '
SELECT a.Seq,
       a.Series,
       a.Series_Nm,
       a.Prod_No,
       a.Prod_Nm,
       a.Price,
       a.Brick_Cnt,
       a.Brick_Weight,
       a.Figure_Cnt,
       a.Buy_Dt,
       a.Contents,
       a.Keyword,
       a.Attach_File_Info
  FROM (
       SELECT ROW_NUMBER() OVER(ORDER BY ' + @order_by + @ls_asc_desc + ', Prod_Nm) AS Rnum,
              Seq,
              Series,
              dbo.ufn_UPDMS_Get_Code_Name(Series, ''ETC'', @ls_lang_set) AS Series_Nm,
              Prod_No,
              Prod_Nm,
              Price,
              Brick_Cnt,
              Brick_Weight,
              Figure_Cnt,
              Buy_Dt,
              Contents,
              Keyword,
              CASE WHEN dbo.ufn_UPDMS_Get_Exist_Attach_File_Image(Seq, ''Etc1014m'') <= 0 THEN ''''
                   ELSE ''<div title="첨부파일" class="sbtn sbtn_fdisk" onclick="popupMultiImage('''''' + CONVERT(VARCHAR, Seq) + '''''',''''Etc1014m'''');"></div>''
              END Attach_File_Info
         FROM UPDMS_ETC_LEGO_MGT WITH(NOLOCK)
        WHERE Buy_Dt >= @ls_buy_dt_from
          AND Buy_Dt <= @ls_buy_dt_to
          AND (Series IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@series))
               OR Series LIKE @series
              )
          AND Prod_No LIKE ''%'' + @ls_prod_no + ''%''
          AND Prod_Nm LIKE ''%'' + @ls_prod_nm + ''%''
          AND Contents LIKE ''%'' + @ls_contents + ''%''
       ) a
 WHERE a.Rnum > CONVERT(NVARCHAR, @li_st_page) AND a.Rnum <= CONVERT(NVARCHAR, @li_ed_page)
 '


DECLARE @param nvarchar(max)
SET @param = '
  @li_st_page int,
  @li_ed_page int,
  @ls_buy_dt_from nvarchar(10),
  @ls_buy_dt_to nvarchar(10),
  @ls_series nvarchar(max),
  @ls_prod_no varchar(10),
  @ls_prod_nm nvarchar(200),
  @ls_contents nvarchar(max),
  @ls_order_by nvarchar(5),
  @ls_asc_desc nvarchar(5),
  @ls_lang_set nvarchar(2),
  @series nvarchar(max)
'

--print(@sql)
EXEC sp_executesql @sql, @param,
                   @li_st_page = @li_st_page,
                   @li_ed_page = @li_ed_page,
                   @ls_buy_dt_from = @ls_buy_dt_from,
                   @ls_buy_dt_to = @ls_buy_dt_to,
                   @ls_series = @ls_series,
                   @ls_prod_no = @ls_prod_no,
                   @ls_prod_nm = @ls_prod_nm,
                   @ls_contents = @ls_contents,
                   @ls_order_by = @ls_order_by,
                   @ls_asc_desc = @ls_asc_desc,
                   @ls_lang_set = @ls_lang_set,
                   @series = @series

GO